package com.lc.cn.managersys.dao.SysMaintain;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.lc.cn.managersys.common.JdbcTemplate;

import org.springframework.stereotype.Component;
import com.lc.cn.managersys.model.CategoryForm;

/**
 * category maintain
 * 
 */
@Component
public class CategoryMaintainDao {

	private static final String LIST = "(SELECT id, category_name, category_desc, category_kind FROM t_category WHERE prio_id = ?)"
			+ "UNION ALL (SELECT id, ch_name as  category_name, remark_ch as category_desc, 3 as  category_kind FROM t_component WHERE unit_id = ?)";
	private static final String LIST_CATEGORY_IDS = "SELECT id FROM t_category WHERE prio_id = ?";
	private static final String LIST_VECHILE_SERIES = "SELECT * FROM t_category where prio_id = 0";
	private static final String QUERY = "SELECT * FROM t_category WHERE id=?";
	private static final String INSERT_CATEGORY = "INSERT INTO t_category(category_name, category_desc, category_kind, prio_id)values(?, ?, ?, ?)";
	private static final String UPDATE_CATEGORY = "UPDATE t_category SET category_name=?,category_desc=? WHERE id=?";
	private static final String DELETE_CATEGORY = "DELETE FROM t_category WHERE id=?";
	private static final String DELECT_COMPONENT = "DELETE FROM t_component WHERE id=?";
	
	@Autowired
	private JdbcTemplate jdbcTemplate = null;

	@SuppressWarnings("unchecked")
	public String listCategory(Integer prioId) {
		StringBuilder json = new StringBuilder();
		List<Map<String, Object>> list = jdbcTemplate.queryForList(LIST,
				new Object[] { prioId, prioId });

		if (list != null && list.size() > 0) {
			json.append("hasChildren_[");
			for (Map map : list) {
				Integer id = (Integer) map.get("id");
				String text = map.get("category_name") == null ? ""
						: (String) map.get("category_name");
				Long kind = (Long) map.get("category_kind");
				json.append("{\"id\":");
				json.append(id);
				json.append(",\"text\":\"");
				json.append(text);
				json.append("\",\"attributes\":");
				json.append(kind);

				String jsonTemp = listCategory(id);
				if (jsonTemp.indexOf("hasChildren_") >= 0) {
					json.append(",\"children\":");
					json.append(jsonTemp);
				}

				json.append("},");
			}
		}

		String str = json.toString();

		if (str != null && str.trim().length() > 0) {
			if (str.lastIndexOf(",") > 0) {
				return (str.substring(0, str.lastIndexOf(",")) + "]");
			}
		}

		return "";
	}
	
	public void batchDeleteCategory(final List<Integer> ids) {
		jdbcTemplate.batchUpdate(DELETE_CATEGORY, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.size();
			}

			@Override
			public void setValues(PreparedStatement preparedstatement, int i)
					throws SQLException {
				preparedstatement.setInt(1, ids.get(i));
			}});
	}
	
	public void batchDeleteComponent(final List<Integer> ids) {
		jdbcTemplate.batchUpdate(DELECT_COMPONENT, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.size();
			}

			@Override
			public void setValues(PreparedStatement preparedstatement, int i)
					throws SQLException {
				preparedstatement.setInt(1, ids.get(i));
			}});
	}

	public CategoryForm getCategory(Integer categoryId) {
		List<CategoryForm> list = jdbcTemplate.query(QUERY,
				new Object[] { categoryId }, new RowMapper<CategoryForm>() {

					@Override
					public CategoryForm mapRow(ResultSet resultset, int i)
							throws SQLException {
						CategoryForm category = new CategoryForm();
						category.setId(resultset.getInt("id"));
						category.setPreId(resultset.getInt("id"));
						category.setCategoryName(resultset
								.getString("category_name"));
						category.setCategoryDesc(resultset
								.getString("category_desc"));
						category.setCategoryKind(resultset
								.getInt("category_kind"));
						return category;
					}

				});

		if (list != null && list.size() > 0)
			return list.get(0);

		return null;
	}

	public void deleteCategory(Integer categoryId) {
		jdbcTemplate.update(DELETE_CATEGORY, new Object[] { categoryId });
		
	}

	public void updateCategory(Integer categoryId, String categoryName,
			String categoryDesc) {
		jdbcTemplate.update(UPDATE_CATEGORY, new Object[] { categoryName,
				categoryDesc, categoryId });
	}

	public long insertCategory(int preId, String categoryName,
			String categoryDesc, Integer categoryKind) {
		return jdbcTemplate.insert(INSERT_CATEGORY, new Object[] { categoryName,
				categoryDesc, categoryKind, preId });
	}
	
	public List<Integer> getCategoryIds(Integer prioId) {
		return jdbcTemplate.queryForList(LIST_CATEGORY_IDS, Integer.class, new Object[]{prioId});
	}

	public List<CategoryForm> listVechileSeries() {
		List<CategoryForm> vechiles = jdbcTemplate.query(LIST_VECHILE_SERIES,
				new RowMapper<CategoryForm>() {

					@Override
					public CategoryForm mapRow(ResultSet resultset, int arg1)
							throws SQLException {
						CategoryForm category = new CategoryForm();
						category.setId(resultset.getInt("id"));
						category.setCategoryName(resultset
								.getString("category_name"));
						category.setCategoryDesc(resultset
								.getString("category_desc"));

						return category;
					}

				});

		if (vechiles != null && vechiles.size() > 0) {
			return vechiles;
		}

		return null;
	}

	public static void main(String[] args) {
		AbstractApplicationContext ctx = new ClassPathXmlApplicationContext(
				"classpath*:applicationContext.xml");
		CategoryMaintainDao dao = (CategoryMaintainDao) ctx
				.getBean("categoryMaintainDao");
		// System.out.println(dao.listCategory(0).replaceAll("hasChildren_",
		// ""));
		//
		// System.out.println(dao.getCategory(0));
		// System.out.println("hasChildren_[{aaaa".indexOf("hasChildren_") >=
		// 0);

		//dao.listVechileSeries();
		
//		List<Integer> ids = dao.getCategoryIds(0);
//		System.out.println(ids.size());
		
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(24);
		ids.add(26);
		
		dao.batchDeleteCategory(ids);

	}
}
